package com.sunwayworld.common.utils.sql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import com.sunwayworld.common.utils.BeanUtils;
import com.sunwayworld.common.utils.ConnManager;
import com.sunwayworld.common.utils.QueryUtils;
import com.sunwayworld.common.utils.SplitPage;
import com.sunwayworld.common.utils.VCommList;

public class QuerySQLServer implements QueryUtils {

	public QuerySQLServer() {

	}

	public Object getObject(String sqlStr, Class className) {
		System.out.println("--\n" + sqlStr);

		Object obj = null;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			obj = className.newInstance();
			conn = ConnManager.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			rs = stmt.executeQuery(sqlStr);

			if (rs.next()) {

				BeanUtils.copyProperties(rs, obj);
			}

			return obj;

		} catch (Exception ex) {
			ex.printStackTrace();
			return obj;
		} finally {
			ConnManager.close(conn, stmt, rs);
			conn = null;
			stmt = null;
			rs = null;
		}
	}

	/**
	 * 不分页的
	 * 
	 * @param sqlStr
	 * @param className
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public VCommList getVCommList(String sqlStr, Class className) {
		System.out.println("--\n" + sqlStr);
		VCommList vlist = new VCommList();

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		int num = 0; // 总行数

		try {
			conn = ConnManager.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			rs = stmt.executeQuery(sqlStr);
			if (rs == null) {
				return vlist;
			}

			int row = 0;
			Object obj = null;
			while (rs.next()) {
				obj = className.newInstance();
				BeanUtils.copyProperties(rs, obj);
				row++;

				vlist.add(obj);
			}

			if (rs.last()) {
				num = rs.getRow();
			} else {
				num = 0;
			}
			vlist.setCurrPage(1);
			vlist.setNum(num);
			vlist.setNumPerPage(num);
			vlist.setPageNum(1);

			return vlist;

		} catch (Exception ex) {
			ex.printStackTrace();
			return vlist;
		} finally {
			ConnManager.close(conn, stmt, rs);
			conn = null;
			stmt = null;
			rs = null;
		}

	}

	/**
	 * 分页的
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public VCommList getVCommList(String sqlStr, Class className,
			SplitPage spage) {

		VCommList vlist = new VCommList();
		if (null == spage) {
			return getVCommList(sqlStr, className);
		}
		System.out.println("--\n" + sqlStr);
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		int num = 0; // 总行数

		int currPage = spage.getCurrPage();// 当前第currpage页
		int numPerPage = spage.getNumPerPage();// 每页多少行
		int rowIndex = numPerPage * (currPage - 1) + 1;// 从第几行开始查询

		try {
			conn = ConnManager.getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

			rs = stmt.executeQuery(sqlStr);
			if (rs == null) {
				return null;
			}

			int row = 0;
			rs.absolute(rowIndex);
			rs.previous();
			Object obj = null;
			while (rs.next() && row < numPerPage) {
				obj = className.newInstance();
				BeanUtils.copyProperties(rs, obj);
				row++;
				vlist.add(obj);
			}

			if (rs.last()) {
				num = rs.getRow();
			} else {
				num = 0;
			}

			fillCommList(vlist, spage, num);
			return vlist;

		} catch (Exception ex) {
			ex.printStackTrace();
			return vlist;
		} finally {
			ConnManager.close(conn, stmt, rs);
			conn = null;
			stmt = null;
			rs = null;
		}

	}

	private void fillCommList(VCommList vlist, SplitPage spage, int nCount) {

		if (vlist == null) {
			return;
		}
		if (spage == null) {
			spage=new SplitPage();
			spage.setNumPerPage(SplitPage.CONST_NUMPERPAGE);
			spage.setCurrPage(SplitPage.CONST_CURRPAGE);
		}
		vlist.setNumPerPage(spage.getNumPerPage());
		vlist.setCurrPage(spage.getCurrPage());
		vlist.setNum(nCount);
		vlist.setPageNum(nCount / vlist.getNumPerPage());
		if ((vlist.getPageNum() * vlist.getNumPerPage()) < nCount)
			vlist.setPageNum(vlist.getPageNum() + 1);
	}

}
